using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OracleClient;

using DQS.EL;

namespace DQS.DAL
{
    public static class DQ_OrganizationsService
    {
        public static IList<DQ_Organization> SelectOrgaByLevel(int argLevel)
        {
            IList<DQ_Organization> _objs = new List<DQ_Organization>();
            string sqlStr = "SELECT orga_name,orga_id FROM DQ_Organizations "
                + " WHERE orga_level = :orga_level AND Orga_Status = 'Y' ORDER BY orga_id";
            OracleParameter[] _params = {
                new OracleParameter(":orga_level",OracleType.Number)
            };
            _params[0].Value = argLevel;
            using(OracleDataReader odr = OracleHelper.ExecuteReader(sqlStr,CommandType.Text,_params)){
                while (odr.Read())
                {
                    DQ_Organization _obj = new DQ_Organization();
                    _obj.Orga_Name = odr.GetString(0);
                    _obj.Orga_ID = odr.GetInt32(1);
                    _objs.Add(_obj);
                }
            }
            return _objs;
        }

        public static IList<DQ_Organization> GetSunByParent(int argPid)
        {
            IList<DQ_Organization> _objs = new List<DQ_Organization>();
            string sqlStr = "SELECT orga_name,orga_id FROM DQ_Organizations"
                + " WHERE Sup_ID = :Sup_ID AND Orga_Status = 'Y'";
            OracleParameter[] _params = { 
                new OracleParameter(":Sup_ID",OracleType.Number)
            };
            _params[0].Value = argPid;
            using (OracleDataReader odr = OracleHelper.ExecuteReader(sqlStr, CommandType.Text, _params))
            {
                while (odr.Read())
                {
                    DQ_Organization _obj = new DQ_Organization();
                    _obj.Orga_Name = odr.GetString(0);
                    _obj.Orga_ID = odr.GetInt32(1);
                    _objs.Add(_obj);
                }
            }
            return _objs;
        }

        public static DQ_Organization GetNameByOrgaID(int argOrgaID)
        {
            DQ_Organization _obj = new DQ_Organization();
            string sqlStr = "SELECT Concat(t2.orga_alias,t1.orga_alias) AS orga_alias "
                           + "      ,t1.orga_name,COALESCE(t2.orga_name,' ') AS SupName,t1.orga_level,t1.orga_id "
                           +  "FROM DQ_Organizations t1 "
                           +  "LEFT JOIN DQ_Organizations t2 "
                           +  "  ON t1.sup_id = t2.orga_id "
                           + "  AND t2.orga_level = 4 "
                           + "WHERE t1.orga_id = :orga_id";
            OracleParameter[] _params = { 
                new OracleParameter(":orga_id", OracleType.Number)
            };
            _params[0].Value = argOrgaID;
            using (OracleDataReader odr = OracleHelper.ExecuteReader(sqlStr, CommandType.Text, _params))
            {
                if (odr.Read())
                {
                    _obj.Orga_Alias = odr.GetString(0);
                    _obj.Orga_Name = odr.GetString(1);
                    DQ_Organization supOrga = new DQ_Organization();
                    supOrga.Orga_Name = odr.GetString(2);                    
                    _obj.Sup_ID = supOrga;
                    _obj.Orga_Level = odr.GetInt32(3);
                    _obj.Orga_ID = odr.GetInt32(4);
                }
            };
            return _obj;
        }
    }
}
